package com.zeltov.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.persistence.EntityNotFoundException;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.zeltov.bo.Car;

@Repository("carDaoSpring")
public class CarDaoImplGenericJPA extends SpringDaoJpa<Car> implements ICarDAO {
	protected static Log log = LogFactory.getLog(CarDaoImplGenericJPA.class);

	public CarDaoImplGenericJPA() {
		super(Car.class);
	}

	@Override
	public Car getCarByName(String name) {
		log.debug("getCarByName!");
		Query query = entityManager
				.createQuery("select car from Car car where car.name = :name");
		query.setParameter("name", name);
		Car car = null;
		try {
			car = (Car) query.getSingleResult();
		} catch (NoResultException e) {
			log.error("No user found for username: " + name);
			throw new EntityNotFoundException("Not person with username "
					+ name + " found");
		}
		return car;

	}

	public Connection getConnection() throws HibernateException {
		Session session = getSession();
		Work work = new Work() {
			
			@Override
			public void execute(Connection connection) throws SQLException {
				String sql = "";
				PreparedStatement prepareStatement = connection.prepareStatement(sql);
				
			}
		};
		session.doWork(work);
		Connection connection = session.connection();
		return connection;
	}

	private Session getSession() {
		Session session = (Session) entityManager.getDelegate();
		return session;
	}

	public String getValueUsingJDBC(long id) {
		ResultSet rs = null;
		Statement statement = null;
		Connection conn = null;
		try {
			conn = getConnection();
			String sqlquery = "select value from VALUE where id=" + id;
			statement = conn.createStatement();
			rs = statement.executeQuery(sqlquery);
			if (null != rs && rs.next()) {
				return rs.getString(1); // resultset starts from 1
			}
			 /* or use hibernate session  
	        Session sess = getSession();  
	        String sqlHibernatequery = "select value from VALUE where id=:id;  
	        SQLQuery sq = sess.createSQLQuery(sqlquery).addScalar("value", StandardBasicTypes.STRING).setLong("id",id);  
	        List<String> valueList = sq.list();  
	        if(null!=valueList && !valueList.isEmpty()){  
	           return valueList.get(0);  
	        }  
	        */  
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// close result set
			// close statement
			// Do not close connection. It wil be closed when transaction
			// finishes
		}
		return null;
	}

}
